﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Estrutura;


namespace Banco
{

    /// <summary>
    /// Summary description for BD_Matricula
    /// </summary>
    public class BD_Matricula
    {
        #region variaveis_globais

        acessaDB.conectaDBMSSQLServer sqlServer = new conectaDBMSSQLServer();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Matricula()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
        }
        public BD_Matricula(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public System.Data.DataSet _retorna_Matricula(
                                                string MATRICULA_ID,
                                                string ALUNO_ID,
                                                string TURMA_ID,
                                                string BOLSA_ID,
                                                string MAT_DTINCLUSAO,
                                                string MAT_DTEXCLUSAO,
                                                string MAT_STEXCLUIDO,
                                                string MODALIDADE_ID,
                                                string MAT_VRMENSALIDADE
                                                )
        {

            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "''";
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "''";
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "''";
            if (string.IsNullOrEmpty(BOLSA_ID)) BOLSA_ID = "''";
            if (string.IsNullOrEmpty(MAT_DTINCLUSAO)) MAT_DTINCLUSAO = "''";
            if (string.IsNullOrEmpty(MAT_DTEXCLUSAO)) MAT_DTEXCLUSAO = "''";
            if (string.IsNullOrEmpty(MAT_STEXCLUIDO)) MAT_STEXCLUIDO = "''";
            if (string.IsNullOrEmpty(MODALIDADE_ID)) MODALIDADE_ID = "''";
            if (string.IsNullOrEmpty(MAT_VRMENSALIDADE)) MAT_VRMENSALIDADE = "''";

            string sql = "SELECT " +
                                  "MATRICULA.MATRICULA_ID, " +
                                  "MATRICULA.ALUNO_ID, " +
                                  "MATRICULA.TURMA_ID, " +
                                  "MATRICULA.BOLSA_ID, " +
                                  "MATRICULA.MAT_DTINCLUSAO, " +
                                  "MATRICULA.MAT_DTEXCLUSAO, " +
                                  "MATRICULA.MAT_STEXCLUIDO, " +
                                  "MATRICULA.MODALIDADE_ID, " +
                                  "MATRICULA.MAT_VRMENSALIDADE " +
                            "FROM MATRICULA " +
                            "LEFT JOIN BOLSA ON BOLSA.BOLSA_ID = MATRICULA.BOLSA_ID " +
                            "LEFT JOIN TURMA ON TURMA.TURMA_ID = MATRICULA.TURMA_ID " +
                            "LEFT JOIN ALUNO ON ALUNO.ALUNO_ID = MATRICULA.ALUNO_ID " +
                            "WHERE " +
                            "      (MATRICULA.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = '') AND " +
                            "      (MATRICULA.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = '') AND " +
                            "      (MATRICULA.TURMA_ID = " + TURMA_ID + "  OR " + TURMA_ID + " = '') AND " +
                            "      (MATRICULA.BOLSA_ID = " + BOLSA_ID + "  OR " + BOLSA_ID + " = '') AND " +
                            "      (MATRICULA.MAT_DTINCLUSAO = " + MAT_DTINCLUSAO + "  OR " + MAT_DTINCLUSAO + " = '') AND " +
                            "      (MATRICULA.MAT_DTEXCLUSAO = " + MAT_DTEXCLUSAO + "  OR " + MAT_DTEXCLUSAO + " = '') AND " +
                            "      (MATRICULA.MAT_STEXCLUIDO = " + MAT_STEXCLUIDO + "  OR " + MAT_STEXCLUIDO + " = '') AND " +
                            "      (MATRICULA.MODALIDADE_ID = " + MODALIDADE_ID + "  OR " + MODALIDADE_ID + " = '') AND " +
                            "      (MATRICULA.MAT_VRMENSALIDADE = " + MAT_VRMENSALIDADE + "  OR " + MAT_VRMENSALIDADE + " = '') ";

            return sqlServer.retornaDataSet(sql, conectionString);;
            

        }


        public List<ES_Matricula> _obter_Matricula(
                                                string MATRICULA_ID,
                                                string ALUNO_ID,
                                                string TURMA_ID,
                                                string BOLSA_ID,
                                                string MAT_DTINCLUSAO,
                                                string MAT_DTEXCLUSAO,
                                                string MAT_STEXCLUIDO,
                                                string MODALIDADE_ID,
                                                string MAT_VRMENSALIDADE
                                                )
        {

            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "''"; else MATRICULA_ID = Util.retonarAspas(MATRICULA_ID + " % ");
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "''"; else ALUNO_ID = Util.retonarAspas(ALUNO_ID + " % ");
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "''"; else TURMA_ID = Util.retonarAspas(TURMA_ID + " % ");
            if (string.IsNullOrEmpty(BOLSA_ID)) BOLSA_ID = "''"; else BOLSA_ID = Util.retonarAspas(BOLSA_ID + " % ");
            if (string.IsNullOrEmpty(MAT_DTINCLUSAO)) MAT_DTINCLUSAO = "''"; else MAT_DTINCLUSAO = Util.retonarAspas(MAT_DTINCLUSAO + " % ");
            if (string.IsNullOrEmpty(MAT_DTEXCLUSAO)) MAT_DTEXCLUSAO = "''"; else MAT_DTEXCLUSAO = Util.retonarAspas(MAT_DTEXCLUSAO + " % ");
            if (string.IsNullOrEmpty(MAT_STEXCLUIDO)) MAT_STEXCLUIDO = "''"; else MAT_STEXCLUIDO = Util.retonarAspas(MAT_STEXCLUIDO + " % ");
            if (string.IsNullOrEmpty(MODALIDADE_ID)) MODALIDADE_ID = "''"; else MODALIDADE_ID = Util.retonarAspas(MODALIDADE_ID + " % ");
            if (string.IsNullOrEmpty(MAT_VRMENSALIDADE)) MAT_VRMENSALIDADE = "''"; else MAT_VRMENSALIDADE = Util.retonarAspas(MAT_VRMENSALIDADE + " % ");

            string sql = "SELECT " +
                                  "MATRICULA.MATRICULA_ID, " +
                                  "MATRICULA.ALUNO_ID, " +
                                  "MATRICULA.TURMA_ID, " +
                                  "MATRICULA.BOLSA_ID, " +
                                  "MATRICULA.MAT_DTINCLUSAO, " +
                                  "MATRICULA.MAT_DTEXCLUSAO, " +
                                  "MATRICULA.MAT_STEXCLUIDO, " +
                                  "MATRICULA.MODALIDADE_ID, " +
                                  "MATRICULA.MAT_VRMENSALIDADE " +
                            "FROM MATRICULA " +
                            "LEFT JOIN BOLSA ON BOLSA.BOLSA_ID = MATRICULA.BOLSA_ID " +
                            "LEFT JOIN TURMA ON TURMA.TURMA_ID = MATRICULA.TURMA_ID " +
                            "LEFT JOIN ALUNO ON ALUNO.ALUNO_ID = MATRICULA.ALUNO_ID " +
                            "WHERE " +
                            "      (MATRICULA.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = '') AND " +
                            "      (MATRICULA.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = '') AND " +
                            "      (MATRICULA.TURMA_ID = " + TURMA_ID + "  OR " + TURMA_ID + " = '') AND " +
                            "      (MATRICULA.BOLSA_ID = " + BOLSA_ID + "  OR " + BOLSA_ID + " = '') AND " +
                            "      (MATRICULA.MAT_DTINCLUSAO = " + MAT_DTINCLUSAO + "  OR " + MAT_DTINCLUSAO + " = '') AND " +
                            "      (MATRICULA.MAT_DTEXCLUSAO = " + MAT_DTEXCLUSAO + "  OR " + MAT_DTEXCLUSAO + " = '') AND " +
                            "      (MATRICULA.MAT_STEXCLUIDO = " + MAT_STEXCLUIDO + "  OR " + MAT_STEXCLUIDO + " = '') AND " +
                            "      (MATRICULA.MODALIDADE_ID = " + MODALIDADE_ID + "  OR " + MODALIDADE_ID + " = '') AND " +
                            "      (MATRICULA.MAT_VRMENSALIDADE = " + MAT_VRMENSALIDADE + "  OR " + MAT_VRMENSALIDADE + " = '') ";

            List<ES_Matricula> lista = new List<ES_Matricula>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Matricula item = new ES_Matricula();
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.MAT_DTINCLUSAO = dr["MAT_DTINCLUSAO"].ToString();
                    item.MAT_DTEXCLUSAO = dr["MAT_DTEXCLUSAO"].ToString();
                    item.MAT_STEXCLUIDO = dr["MAT_STEXCLUIDO"].ToString();
                    item.MODALIDADE_ID = dr["MODALIDADE_ID"].ToString();
                    item.MAT_VRMENSALIDADE = dr["MAT_VRMENSALIDADE"].ToString();
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public List<ES_Matricula> _obter_Matricula_Aluno(string ALUNO_ID, bool TODAS = false)
        {
            //Validar Inserção de Aluno
            if (ALUNO_ID == "NEW")
                return new List<ES_Matricula>();

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            
            string sql = "SELECT     M.MATRICULA_ID, "+
                         "           ISNULL(T.TUR_DESCRICAO,'')+' .:. '+ISNULL(D.MOD_DESCRICAO,'')+' .:. '+ISNULL(N.NIV_DESCRICAO,'') MAT_MTRDESCRICAO, "+
                         "           (SELECT TURMA.TUR_DESCRICAO+' .:. '+ISNULL(MODALIDADE.MOD_DESCRICAO,'')+' .:. '+ISNULL(NIVEL.NIV_DESCRICAO,'')+' .:. '+ISNULL(TURNO.TUR_DESCRICAO,'') FROM TURMA LEFT JOIN MODALIDADE ON MODALIDADE.MODALIDADE_ID = TURMA.MODALIDADE_ID LEFT JOIN NIVEL ON NIVEL.NIVEL_ID = TURMA.NIVEL_ID LEFT JOIN TURNO ON TURNO.TURNO_ID = TURMA.TURNO_ID WHERE TURMA.TURMA_ID = M.TURMA_ID) AS TUR_DESCRICAOCOMP, " +
		                 "           M.ALUNO_ID, " +
		                 "           M.TURMA_ID,	 " +	
		                 "           T.TUR_DESCRICAO, " +
		                 "           M.BOLSA_ID, " +
		                 "           B.BOL_DESCRICAO, " +
		                 "           M.MAT_DTINCLUSAO, " +
		                 "           M.MAT_DTEXCLUSAO, " +
		                 "           M.MODALIDADE_ID, " +
		                 "           D.MOD_DESCRICAO, " +
                         "           M.NIVEL_ID, "+
                         "           N.NIV_DESCRICAO, "+
		                 "           M.MAT_VRMENSALIDADE, " +
                         "           M.MAT_STEXCLUIDO, " +
                         "           M.MAT_DIAVENCIMENTO, "+
                         "           ISNUll(M.MAT_ISENTOTXPARTICIPACAO,'N') MAT_ISENTOTXPARTICIPACAO, " +
		                 "           M.MAT_TXPARTICIPACAO, "+
                         "           (SELECT COUNT(*) FROM MATRICULA WHERE ALUNO_ID = "+ALUNO_ID+") MAT_TOTALMATRICULA " +
                         "FROM MATRICULA M  " +
                         "INNER JOIN TURMA T ON (T.TURMA_ID = M.TURMA_ID) " +
                         "LEFT JOIN BOLSA B ON (B.BOLSA_ID = M.BOLSA_ID) " +
                         "LEFT JOIN MODALIDADE D ON (D.MODALIDADE_ID = M.MODALIDADE_ID) " +
                         "LEFT JOIN NIVEL N ON (M.NIVEL_ID = N.NIVEL_ID) "+
                         " WHERE " +
                         " M.MAT_STEXCLUIDO = 0 AND "+
                         "      (M.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) ";

            List<ES_Matricula> lista = new List<ES_Matricula>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);

            //acionar todas quando houver mais de uma Matrícula ativa
            int linhas = 0;
                       
            try
            {
                while (dr.Read())
                {
                    if (linhas == 0 && Convert.ToInt32(dr["MAT_TOTALMATRICULA"].ToString()) > 1 && TODAS)
                    {
                        ES_Matricula itemM = new ES_Matricula();
                        itemM.MATRICULA_ID = "-1";
                        itemM.MAT_MTRDESCRICAO = "Todas";
                        itemM.ALUNO_ID = ALUNO_ID;
                        itemM.TURMA_ID = string.Empty;
                        itemM.TUR_DESCRICAO = "Todas";
                        itemM.BOLSA_ID = string.Empty;
                        itemM.BOL_DESCRICAO = string.Empty;
                        itemM.MAT_DTINCLUSAO = string.Empty;
                        itemM.MAT_DTEXCLUSAO = string.Empty;
                        itemM.MAT_STEXCLUIDO = "0";
                        itemM.MODALIDADE_ID = string.Empty;
                        itemM.MOD_DESCRICAO = string.Empty;
                        itemM.MAT_VRMENSALIDADE = string.Empty;
                        itemM.MAT_VRMENSALIDADE = string.Empty;
                        itemM.MAT_VRMENSALIDADE = string.Empty;
                        itemM.MAT_DIAVENCIMENTO = string.Empty;
                        itemM.MAT_ISENTOTXPARTICIPACAO = string.Empty;
                        itemM.MAT_TXPARTICIPACAO = string.Empty;

                        lista.Add(itemM);
                        linhas++;
                    }

                    ES_Matricula item = new ES_Matricula();
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.MAT_MTRDESCRICAO = dr["MAT_MTRDESCRICAO"].ToString();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString();
                    item.TUR_DESCRICAOCOMP = dr["TUR_DESCRICAOCOMP"].ToString();
                    item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.BOL_DESCRICAO = dr["BOL_DESCRICAO"].ToString();
                    item.MAT_DTINCLUSAO = dr["MAT_DTINCLUSAO"].ToString().Replace("00:00:00", "").Trim();
                    item.MAT_DTEXCLUSAO = dr["MAT_DTEXCLUSAO"].ToString().Replace("00:00:00", "").Trim();
                    item.MAT_STEXCLUIDO = dr["MAT_STEXCLUIDO"].ToString();
                    item.MODALIDADE_ID = dr["MODALIDADE_ID"].ToString();
                    item.MOD_DESCRICAO = dr["MOD_DESCRICAO"].ToString();
                    item.MAT_VRMENSALIDADE = dr["MAT_VRMENSALIDADE"].ToString();
                    item.NIVEL_ID = dr["NIVEL_ID"].ToString();
                    item.NIV_DESCRICAO = dr["NIV_DESCRICAO"].ToString();
                    item.MAT_DIAVENCIMENTO = dr["MAT_DIAVENCIMENTO"].ToString();
                    item.MAT_ISENTOTXPARTICIPACAO = dr["MAT_ISENTOTXPARTICIPACAO"].ToString();
                    item.MAT_TXPARTICIPACAO = dr["MAT_TXPARTICIPACAO"].ToString();
                    
                    lista.Add(item);
                    linhas++;

                }
                
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public List<ES_Matricula> _obter_Matricula_Turma(string TURMA_ID)
        {
            //Validar Inserção de Aluno
            if (TURMA_ID == "NEW")
                return new List<ES_Matricula>();

            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "0";

            string sql = "SELECT M.MATRICULA_ID, " +
                         "       M.ALUNO_ID, " +
                         "       P.PES_NOME, " +
                         "       M.TURMA_ID, " +
                         "       T.TUR_DESCRICAO, " +
                         "       M.MAT_DTINCLUSAO, " +
                         "       M.BOLSA_ID, " +
                         "       B.BOL_DESCRICAO, " +
                         "       B.BOL_MODALIDADE " +
                         "FROM MATRICULA M " +
                         "INNER JOIN ALUNO A ON (A.ALUNO_ID = M.ALUNO_ID) " +
                         "INNER JOIN PESSOA P ON (P.PESSOA_ID = A.PESSOA_ID) " +
                         "INNER JOIN TURMA T ON (M.TURMA_ID = T.TURMA_ID) " +
                         "LEFT JOIN BOLSA B ON (B.BOLSA_ID = M.BOLSA_ID) " +
                         " WHERE " +
                         "      ISNULL(A.ALU_STEXCLUIDO,0) = 0 AND " +
                         "      ISNULL(M.MAT_STEXCLUIDO,0) = 0 AND " +
                         "      (M.TURMA_ID = " + TURMA_ID + "  OR " + TURMA_ID + " = 0) " +
                         "ORDER BY M.TURMA_ID, P.PES_NOME ";

            List<ES_Matricula> lista = new List<ES_Matricula>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Matricula item = new ES_Matricula();
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString();
                    item.MAT_DTINCLUSAO = Util.formataData(dr["MAT_DTINCLUSAO"].ToString(), "dd/MM/yyyy", true);                    
                    item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.BOL_DESCRICAO = dr["BOL_DESCRICAO"].ToString();
                    item.BOL_MODALIDADE = dr["BOL_MODALIDADE"].ToString();
                                        

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public List<ES_Matricula> _obter_Matricula_Geral(string ALUNO_ID,
                                                        string MATRICULA_ID,
                                                        string PES_NOME,
                                                        string MAT_STEXCLUIDO, 
                                                        string MAT_DTINCLUSAO, 
                                                        string MAT_DTMES, 
		                                                string MAT_DTANO,
                                                        string BOLSISTA,
                                                        string ORDER_BY_CLAUSE
                                                       )
        {
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (string.IsNullOrEmpty(MAT_STEXCLUIDO)) MAT_STEXCLUIDO = "-1";
            if (!string.IsNullOrEmpty(MAT_DTINCLUSAO)) MAT_DTINCLUSAO = Util.retonarAspas(Util.formataData(MAT_DTINCLUSAO, "dd/MM/yyyy"));
            if (string.IsNullOrEmpty(MAT_DTMES)) MAT_DTMES = "0";
            if (string.IsNullOrEmpty(MAT_DTANO)) MAT_DTANO = "0";
            if (string.IsNullOrEmpty(BOLSISTA)) BOLSISTA = "-1";
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "M.PES_NOME ";

            string sql = "SELECT ALUNO_ID, "+
		                 "       MATRICULA_ID,  "+
		                 "       MODALIDADE_ID,  "+
		                 "       MAT_STEXCLUIDO,  "+
		                 "       MAT_DTINCLUSAO,  "+
		                 "       MAT_DTMES,  "+
		                 "       MAT_DTANO,  "+
		                 "       MOD_DESCRICAO,  "+
		                 "       NIVEL_ID,  "+
		                 "       NIV_DESCRICAO,  "+
		                 "       TURMA_ID,  "+
		                 "       TUR_DESCRICAO,  "+
		                 "       TURNO_ID,  "+
		                 "       TURNO_DESCRICAO,  "+
		                 "       USUARIO_ID,  "+
		                 "       USU_NOME,  "+
		                 "       PESSOA_ID,  "+
		                 "       PES_NOME,  "+
                         "       PES_DTNASCIMENTO, " +
                         "       CASE WHEN BOLSISTA > 0 THEN 'S' ELSE 'N' END BOLSISTA, " +
                         "       CASE WHEN BOLSISTA_INTEGRAL > 0 THEN 'S' ELSE 'N' END BOLSISTA_INTEGRAL " +
                         "FROM VW_MATRICULA M " +
                         " WHERE " +
                         "      (M.TURMA_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND " +
                         "      (M.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = 0) AND " +
                         "      (M.PES_NOME LIKE " + PES_NOME + "  OR " + PES_NOME + " = '') AND " +
                         "      (M.MAT_STEXCLUIDO = " + MAT_STEXCLUIDO + "  OR " + MAT_STEXCLUIDO + " = -1) AND ";
            
            if (!string.IsNullOrEmpty(MAT_DTINCLUSAO))
                sql += "      (M.MAT_DTINCLUSAO = " + MAT_DTINCLUSAO + ") AND ";

            sql += "      (M.MAT_DTMES = " + MAT_DTMES + "  OR " + MAT_DTMES + " = 0) AND " +
                         "      (M.MAT_DTANO = " + MAT_DTANO + "  OR " + MAT_DTANO + " = 0) AND " +
                         "      (M.BOLSISTA = " + BOLSISTA + "  OR " + BOLSISTA + " = -1) " +
                         "ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Matricula> lista = new List<ES_Matricula>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Matricula item = new ES_Matricula();
                    
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString(); 
		            item.MATRICULA_ID = dr["MATRICULA_ID"].ToString(); 
		            item.MODALIDADE_ID = dr["MODALIDADE_ID"].ToString(); 
		            item.MAT_STEXCLUIDO = dr["MAT_STEXCLUIDO"].ToString();
                    item.MAT_DTINCLUSAO = Util.formataData(dr["MAT_DTINCLUSAO"].ToString(), "dd/MM/yyyy", true);
                    item.MAT_DTMES = dr["MAT_DTMES"].ToString();
                    item.MAT_DTANO = dr["MAT_DTANO"].ToString();
                    item.MOD_DESCRICAO = dr["MOD_DESCRICAO"].ToString();
                    item.NIVEL_ID = dr["NIVEL_ID"].ToString();
                    item.NIV_DESCRICAO = dr["NIV_DESCRICAO"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString();
                    item.TURNO_ID = dr["TURNO_ID"].ToString();
                    item.TURNO_DESCRICAO = dr["TURNO_DESCRICAO"].ToString();
                    item.USUARIO_ID = dr["USUARIO_ID"].ToString();
                    item.USU_NOME = dr["USU_NOME"].ToString();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.PES_DTNASCIMENTO = Util.formataData(dr["PES_DTNASCIMENTO"].ToString(), "dd/MM/yyyy", true);
                    item.BOLSISTA = ((dr["BOLSISTA"].ToString() == "S") ? true : false);
                    item.BOLSISTA_INTEGRAL = ((dr["BOLSISTA_INTEGRAL"].ToString() == "S") ? true : false);

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public ES_Matricula _obter_Matricula_by_id(string MATRICULA_ID, string ALUNO_ID= "")
        {

            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";

            string sql = "SELECT M.MATRICULA_ID, " +
                         "           M.ALUNO_ID, " +
                         "           M.TURMA_ID,	 " +
                         "           T.TUR_DESCRICAO, " +
                         "           M.BOLSA_ID, " +
                         "           B.BOL_DESCRICAO, " +
                         "           M.MAT_DTINCLUSAO, " +
                         "           M.MAT_DTEXCLUSAO, " +
                         "           M.MODALIDADE_ID, " +
                         "           D.MOD_DESCRICAO, " +
                         "           M.NIVEL_ID, "+   
                         "           M.MAT_DIAVENCIMENTO, "+
                         "           M.MAT_TXPARTICIPACAO, " +
                         "           M.MAT_VRMENSALIDADE, " +
                         "           ISNULL(M.MAT_ISENTOTXPARTICIPACAO,0) MAT_ISENTOTXPARTICIPACAO, "+
                         "           M.MAT_STEXCLUIDO, " +
                         "           S.SAL_DESCRICAO, "+
                         "           TN.TUR_DESCRICAO TURNO_DESCRICAO, "+
                         "           T.TUR_VALORREFERENCIA "+
                         "   FROM MATRICULA M  " +
                         "   INNER JOIN TURMA T ON (T.TURMA_ID = M.TURMA_ID) " +
                         "   LEFT JOIN SALA S ON (S.SALA_ID = T.SALA_ID) "+
                         "   LEFT JOIN TURNO TN ON (TN.TURNO_ID = T.TURNO_ID) "+
                         "   LEFT JOIN BOLSA B ON (B.BOLSA_ID = M.BOLSA_ID) " +
                         "   LEFT JOIN MODALIDADE D ON (D.MODALIDADE_ID = M.MODALIDADE_ID) " +
                         " WHERE " +
                         "      (M.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = 0) AND"+
                         "      (M.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) "+
                         "ORDER BY M.MATRICULA_ID DESC";//obter sempre o ultimo registro inserido

            ES_Matricula item = new ES_Matricula();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString();
                    item.BOLSA_ID = dr["BOLSA_ID"].ToString();
                    item.BOL_DESCRICAO = dr["BOL_DESCRICAO"].ToString();
                    item.MAT_DTINCLUSAO = dr["MAT_DTINCLUSAO"].ToString();
                    item.MAT_DTEXCLUSAO = dr["MAT_DTEXCLUSAO"].ToString();
                    item.MAT_STEXCLUIDO = dr["MAT_STEXCLUIDO"].ToString();
                    item.MODALIDADE_ID = dr["MODALIDADE_ID"].ToString();
                    item.MOD_DESCRICAO = dr["MOD_DESCRICAO"].ToString();
                    item.NIVEL_ID = dr["NIVEL_ID"].ToString();
                    item.MAT_ISENTOTXPARTICIPACAO = dr["MAT_ISENTOTXPARTICIPACAO"].ToString();
                    item.MAT_DIAVENCIMENTO = dr["MAT_DIAVENCIMENTO"].ToString();
                    item.MAT_VRMENSALIDADE = dr["MAT_VRMENSALIDADE"].ToString();
                    item.SAL_DESCRICAO = dr["SAL_DESCRICAO"].ToString();
                    item.TURNO = dr["TURNO_DESCRICAO"].ToString();
                    item.TUR_VALORREFERENCIA = dr["TUR_VALORREFERENCIA"].ToString();
                }
                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public ES_Matricula _obter_Todas_Matriculas_by_Alunoid(string ALUNO_ID)
        {            
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (ALUNO_ID == "0")
                return new ES_Matricula();

            string sql = "SELECT M.MATRICULA_ID, " +
                         "           M.ALUNO_ID, " +
                         "           M.TURMA_ID,	 " +
                         "           T.TUR_DESCRICAO, " +
                         "           M.BOLSA_ID, " +
                         "           B.BOL_DESCRICAO, " +
                         "           M.MAT_DTINCLUSAO, " +
                         "           M.MAT_DTEXCLUSAO, " +
                         "           M.MODALIDADE_ID, " +
                         "           D.MOD_DESCRICAO, " +
                         "           M.MAT_VRMENSALIDADE, " +
                         "           CASE  " +
			             "               WHEN B.BOL_DESCONTOPERC IS NOT NULL THEN M.MAT_VRMENSALIDADE * (1-(B.BOL_DESCONTOPERC/100)) " +
			             "               WHEN B.BOL_DESCONTOVALOR IS NOT NULL THEN M.MAT_VRMENSALIDADE - BOL_DESCONTOVALOR " +
                         "               ELSE M.MAT_VRMENSALIDADE END AS MAT_VRMENSALIDADEDESC, " +
                         "           M.MAT_STEXCLUIDO, " +
                         "           S.SAL_DESCRICAO, " +
                         "           TN.TUR_DESCRICAO TURNO_DESCRICAO, " +
                         "           T.TUR_VALORREFERENCIA " +
                         "   FROM MATRICULA M  " +
                         "   INNER JOIN TURMA T ON (T.TURMA_ID = M.TURMA_ID) " +
                         "   LEFT JOIN SALA S ON (S.SALA_ID = T.SALA_ID) " +
                         "   LEFT JOIN TURNO TN ON (TN.TURNO_ID = T.TURNO_ID) " +
                         "   LEFT JOIN BOLSA B ON (B.BOLSA_ID = M.BOLSA_ID) " +
                         "   LEFT JOIN MODALIDADE D ON (D.MODALIDADE_ID = M.MODALIDADE_ID) " +
                         " WHERE " +                         
                         "      (M.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) " +
                         "ORDER BY M.MATRICULA_ID DESC";//obter sempre o ultimo registro inserido

            int linhas = 0;
            ES_Matricula item = new ES_Matricula();
            //inicialização
            item.MAT_VRMENSALIDADE = "0";
            item.TUR_VALORREFERENCIA = "0";
            item.MAT_VRMENSALIDADEDESC = "0";
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    if (linhas > 0)
                    {
                        item.MATRICULA_ID += "-";                        
                        item.TURMA_ID += "-";
                        item.TUR_DESCRICAO += "-";
                        item.BOLSA_ID += "-";
                        item.BOL_DESCRICAO += "-";
                        item.MAT_DTINCLUSAO += "-";
                        item.MAT_DTEXCLUSAO += "-";
                        item.MAT_STEXCLUIDO += "-";
                        item.MODALIDADE_ID += "-";
                        item.MOD_DESCRICAO += "-";                        
                        item.SAL_DESCRICAO += dr["SAL_DESCRICAO"].ToString();
                        item.TURNO += "-";                        
                    }
                    item.MATRICULA_ID += dr["MATRICULA_ID"].ToString();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.TURMA_ID += dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO += dr["TUR_DESCRICAO"].ToString();
                    item.BOLSA_ID += dr["BOLSA_ID"].ToString();
                    item.BOL_DESCRICAO += dr["BOL_DESCRICAO"].ToString();
                    item.MAT_DTINCLUSAO += Util.formataData(dr["MAT_DTINCLUSAO"].ToString(), "dd/MM/yyyy", true);
                    item.MAT_DTEXCLUSAO += Util.formataData(dr["MAT_DTEXCLUSAO"].ToString(), "dd/MM/yyyy", true);
                    item.MAT_STEXCLUIDO += dr["MAT_STEXCLUIDO"].ToString();
                    item.MODALIDADE_ID += dr["MODALIDADE_ID"].ToString();
                    item.MOD_DESCRICAO += dr["MOD_DESCRICAO"].ToString();
                    item.MAT_VRMENSALIDADEDESC = (Convert.ToDouble(item.MAT_VRMENSALIDADEDESC) + ((!string.IsNullOrEmpty(dr["MAT_VRMENSALIDADEDESC"].ToString()) ? Convert.ToDouble(dr["MAT_VRMENSALIDADEDESC"].ToString()) : 0))).ToString();
                    item.MAT_VRMENSALIDADE = (Convert.ToDouble(item.MAT_VRMENSALIDADE) + ((!string.IsNullOrEmpty(dr["MAT_VRMENSALIDADE"].ToString()) ? Convert.ToDouble(dr["MAT_VRMENSALIDADE"].ToString()) : 0))).ToString();
                    item.SAL_DESCRICAO += dr["SAL_DESCRICAO"].ToString();
                    item.TURNO += dr["TURNO_DESCRICAO"].ToString();
                    item.TUR_VALORREFERENCIA = (Convert.ToDouble(item.TUR_VALORREFERENCIA) + ((!string.IsNullOrEmpty(dr["TUR_VALORREFERENCIA"].ToString()) ? Convert.ToDouble(dr["TUR_VALORREFERENCIA"].ToString()) : 0))).ToString();

                    linhas++;

                }
                //tratamentos pré retorno
                if (item.TUR_VALORREFERENCIA == "0")
                    item.TUR_VALORREFERENCIA = string.Empty;

                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public void _inserir_Matricula(     
                                            string ALUNO_ID,
                                            string TURMA_ID,
                                            string BOLSA_ID,
                                            string MAT_DTINCLUSAO,
                                            string MAT_DTEXCLUSAO,
                                            string MAT_STEXCLUIDO,
                                            string MODALIDADE_ID,
                                            string MAT_VRMENSALIDADE,
                                            string MAT_DATACOBRANCA,
                                            string MAT_ISENTOTXPARTICIPACAO,
                                            string MAT_TXPARTICIPACAO,
                                            string NIVEL_ID,
                                            string MAT_DIAVENCIMENTO
                                        )
        {

            string sql = " INSERT INTO MATRICULA " +
                         "      ( " +
                         "         ALUNO_ID, "+ 
                         "         TURMA_ID, "+ 
                         "         BOLSA_ID,  "+
                         "         MAT_DTINCLUSAO,  "+
                         "         MAT_DTEXCLUSAO,  "+
                         "         MAT_STEXCLUIDO,  "+
                         "         MODALIDADE_ID,  "+
                         "         MAT_VRMENSALIDADE, "+ 
                         "         MAT_DATACOBRANCA,  "+
                         "         MAT_ISENTOTXPARTICIPACAO,  "+
                         "         MAT_TXPARTICIPACAO,  "+
                         "         NIVEL_ID,  "+
                         "         MAT_DIAVENCIMENTO " +
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                            Util.retonarNull(ALUNO_ID) + ", "+ 
                            Util.retonarNull(TURMA_ID) + ", "+ 
                            Util.retonarNull(BOLSA_ID) + ", "+
                            Util.retonarAspas(Util.formataData(MAT_DTINCLUSAO, "dd/MM/yyyy", true), true) + ", "+
                            "NULL, "+ 
                            "0, "+ 
                            Util.retonarNull(MODALIDADE_ID) + ", "+ 
                            Util.retonarNull(MAT_VRMENSALIDADE) + ", "+
                            Util.retonarAspas(Util.formataData(MAT_DATACOBRANCA, "dd/MM/yyyy", true), true) + "," +                            
                            Util.retonarAspas(MAT_ISENTOTXPARTICIPACAO, true) + ", " + 
                            Util.retonarAspas(MAT_TXPARTICIPACAO, true) + ", "+ 
                            Util.retonarNull(NIVEL_ID) + ", "+ 
                            Util.retonarNull(MAT_DIAVENCIMENTO) +
                 "      )";
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _atualizar_Matricula(
                                            string MATRICULA_ID, 
                                            string ALUNO_ID, 
                                            string TURMA_ID, 
                                            string BOLSA_ID, 
                                            string MAT_DTINCLUSAO, 
                                            string MAT_DTEXCLUSAO, 
                                            string MAT_STEXCLUIDO, 
                                            string MODALIDADE_ID, 
                                            string MAT_VRMENSALIDADE, 
                                            string MAT_DATACOBRANCA, 
                                            string MAT_ISENTOTXPARTICIPACAO, 
                                            string MAT_TXPARTICIPACAO, 
                                            string NIVEL_ID,
                                            string MAT_DIAVENCIMENTO
                                                )
        {

            string sql = " UPDATE MATRICULA SET " +
                        "      ALUNO_ID = " + Util.retonarNull(ALUNO_ID) + ", " +
                        "      TURMA_ID = " + Util.retonarNull(TURMA_ID) + ", " +
                        "      BOLSA_ID = " + Util.retonarNull(BOLSA_ID) + ", " +
                        "      MAT_DTINCLUSAO = " + Util.retonarAspas(Util.formataData(MAT_DTINCLUSAO, "dd/MM/yyyy", true), true) + ", ";

            if (MAT_STEXCLUIDO != null)
            {
                sql += "      MAT_STEXCLUIDO = " + Util.retonarNull(MAT_STEXCLUIDO) + ", " +
                       "      MAT_DTEXCLUSAO = " + Util.retonarAspas(Util.formataData(MAT_DTEXCLUSAO, "dd/MM/yyyy", true), true) + ", ";
            }
            
            sql +=
                        "      MODALIDADE_ID = " + Util.retonarNull(MODALIDADE_ID) + ", " +
                        "      MAT_VRMENSALIDADE = " + Util.retonarNull(MAT_VRMENSALIDADE) + ", ";
            
            if (MAT_DATACOBRANCA != null)
                sql += "      MAT_DATACOBRANCA = " + Util.retonarAspas(Util.formataData(MAT_DATACOBRANCA, "dd/MM/yyyy", true), true) + ", ";
            
            sql +=
                        "      MAT_ISENTOTXPARTICIPACAO = " + Util.retonarAspas(MAT_ISENTOTXPARTICIPACAO, true) + ", " +
                        "      MAT_TXPARTICIPACAO = " + Util.retonarNull(MAT_TXPARTICIPACAO) + ", " +
                        "      NIVEL_ID = " + Util.retonarNull(NIVEL_ID) + ", " +
                        "      MAT_DIAVENCIMENTO = " + Util.retonarNull(MAT_DIAVENCIMENTO) +
                         " WHERE MATRICULA_ID =  " + MATRICULA_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        public void _excluir_Matricula(string MATRICULA_ID)
        {
            string sql = "EXECUTE [dbo].[SP_REMOVE_ALUNO_MATRICULA] " + MATRICULA_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}